package Ms

import (
	"encoding/json"
	"errors"
	"fmt"
	"db2s/ETL"
	"db2s/global"
	"math"
	"reflect"
	"runtime"
	"sort"
	"strconv"
	"strings"
)

type TableInfoMeta struct {
}

func (ms TableInfoMeta) AvgRowLength(s global.TablesMetaInfoInput) (any, error) {
	var (
		event       = "[AvgRowLength]"
		tableData   map[string]interface{}
		avgLength   int64
		execTimeout int64
		tableRows   int64
		err         error
		dataLength  int64
	)
	pc, _, _, _ := runtime.Caller(1)
	callingFunc := runtime.FuncForPC(pc)
	callFile, callLine := callingFunc.FileLine(pc)
	callFunc := callingFunc.Name()
	callFuncInfo := fmt.Sprintf("Call functions %v Call File %v:%v", callFunc, callFile, callLine)
	vlog = fmt.Sprintf("(%d) %v %s Query the table avg row length for statistical information of table %v.%v", s.LogSeq, callFuncInfo, event, s.TableInfo.Schema, s.TableInfo.Table)
	WLog.Debug(vlog)
	strSql := fmt.Sprintf("SELECT [tableRows] = p.rows,dataLength = CONVERT(DECIMAL(18,0), SUM(a.used_pages) * 8 * 1024) FROM sys.tables t INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id WHERE t.object_id = OBJECT_ID('%v.%v') AND t.is_ms_shipped = 0 AND i.OBJECT_ID > 255 GROUP BY  t.Name, s.Name, p.Rows", s.TableInfo.Schema, s.TableInfo.Table)
	active := ETL.InstanceActiveS{DBType: DBType, DB: s.DB["single"], SqlMode: true}
	var f1 any
	if execTimeout, f1, err = active.SqlQuery(strSql, ETL.Map); err != nil {
		vlog = fmt.Sprintf("(%d) %v %v The execution of a sql statement failed. sql is {%v},error is {%v}", s.LogSeq, callFuncInfo, event, strSql, err)
		WLog.Warn(vlog)
		return 0, err
	} else {
		tableData = f1.(map[string]interface{})
	}
	if v, ok := tableData["dataLength"]; ok {
		if dataLength, err = strconv.ParseInt(fmt.Sprintf("%v", v), 10, 64); err != nil {
			vlog = fmt.Sprintf("(%d) %v %s Table avg row length  conversion failed of table %v.%v. sql is %s, error is %s", s.LogSeq, callFuncInfo, event, s.TableInfo.Schema, s.TableInfo.Table, strSql, err)
			WLog.Warn(vlog)
			avgLength = 0
			return avgLength, nil
		}
	}
	if v, ok := tableData["tableRows"]; ok {
		if tableRows, err = strconv.ParseInt(fmt.Sprintf("%v", v), 10, 64); err != nil {
			vlog = fmt.Sprintf("(%d) %v %s Table avg row length  conversion failed of table %v.%v. sql is %s, error is %s", s.LogSeq, callFuncInfo, event, s.TableInfo.Schema, s.TableInfo.Table, strSql, err)
			WLog.Warn(vlog)
			avgLength = 0
			return avgLength, nil
		}
	}
	if dataLength > 0 && tableRows > 0 {
		avgLength = dataLength / tableRows
	}
	vlog = fmt.Sprintf("(%d) %v %v A sql statement was executed of table %v.%v is successfully. sql is {%v}, exec timeout is {%vms}", s.LogSeq, callFuncInfo, event, s.TableInfo.Schema, s.TableInfo.Table, strSql, execTimeout)
	WLog.Debug(vlog)
	vlog = fmt.Sprintf("(%d) %v %s The table avg row length  query of table %v.%v is successful. result is {%v}", s.LogSeq, callFuncInfo, event, s.TableInfo.Schema, s.TableInfo.Table, avgLength)
	WLog.Debug(vlog)
	return avgLength, nil
}

func (ms TableInfoMeta) DataSize(s global.TablesMetaInfoInput) (result global.DataSizeReturnResult, err error) {
	var (
		event = "[sqlserverDataSize]"
		f1    any
	)
	active := ETL.InstanceActiveS{DBType: DBType, DB: s.DB["single"], SqlMode: true}
	result.Sql = fmt.Sprintf("SELECT (sum(a.used_pages) * 8) / 1024 as dataSizeMB, 0 as indexSizeMB FROM sys.objects t INNER JOIN sys.schemas s ON t.SCHEMA_ID = s.SCHEMA_ID INNER JOIN  sys.indexes i ON t.OBJECT_ID = i.object_id INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id WHERE s.name = '%v' AND t.NAME = '%v' AND i.OBJECT_ID > 255 AND i.index_id <= 1 and t.type_desc = 'USER_TABLE' GROUP BY s.NAME, t.NAME, t.type_desc, i.object_id, i.index_id, i.name", s.TableInfo.Schema, s.TableInfo.Table)
	if len(s.TableInfo.Schema) == 0 || len(s.TableInfo.BackendTableName) == 0 || len(s.DB) == 0 || active.DB == nil {
		err = errors.New(fmt.Sprintf("%v sql general fail. schema or table is entry. Execution process:{schema:%v,table:%v,sql:%v,shard:%v,db:%v,err:%v}", event, s.TableInfo.Schema, s.TableInfo.BackendTableName, result.Sql, "single", s.DB, err))
		return
	}

	if result.ExecTimeout, f1, err = active.SqlQuery(result.Sql, ETL.Map); err != nil {
		err = errors.New(fmt.Sprintf("%v sql exec fail. Execution process:{sql:%v,shard:%v,db:%v,err:%v}", event, result.Sql, "single", s.DB, err))
		return
	}
	if err = resultTypeCheck(event, ETL.Map, f1); err != nil || f1 == nil {
		if err == nil {
			err = errors.New(fmt.Sprintf("%v result is empty. Execution process:{sql:%v,shard:%v,db:%v,result:%v}", event, result.Sql, "single", s.DB, f1))
		}
		return
	}

	for k, v := range []string{"dataSizeMB", "indexSizeMB"} {
		if _, ok := f1.(map[string]interface{})[v]; !ok {
			err = errors.New(fmt.Sprintf("%v query result not include %v. process:{sql:%v,result:%v}", v, event, result.Sql, f1))
			return
		}
		v1 := fmt.Sprintf("%v", f1.(map[string]interface{})[v])
		if strings.HasPrefix(v1, ".") {
			v1 = fmt.Sprintf("0%v", v1)
		}
		if !isNumeric(v1) {
			err = errors.New(fmt.Sprintf("%v The result contains illegal non-numeric data. process:{sql:%v,result:%v}", event, result.Sql, v1))
			return
		}
		var v2 float64
		if v2, err = strconv.ParseFloat(v1, 64); err != nil {
			err = errors.New(fmt.Sprintf("%v string strconv float64 fail, strconv data is {%v} error is {%v}", event, v1, err))
			return
		}
		switch k {
		case 0:
			result.DataSizeMB = math.Round(v2*100) / 100
		case 1:
			result.IndexSizeMB = math.Round(v2*100) / 100
		}
	}
	return
}

// Comment 获取表的comment信息
func (ms TableInfoMeta) Comment(s global.TablesMetaInfoInput) (any, error) {
	var (
		event        = "[sqlserverComment]"
		err          error
		tableComment string
	)
	strSql := fmt.Sprintf("SELECT ep.value AS comment FROM sys.objects o LEFT JOIN sys.extended_properties ep ON o.object_id = ep.major_id AND ep.minor_id = 0 AND ep.name = 'MS_Description' WHERE o.type = 'U' AND SCHEMA_NAME(o.schema_id) = '%v' AND OBJECT_NAME(o.object_id) = '%v'", s.TableInfo.Schema, s.TableInfo.Table)
	active := ETL.InstanceActiveS{DBType: DBType, DB: s.DB["single"], SqlMode: true}
	var f1 any
	if _, f1, err = active.SqlQuery(strSql, ETL.Map); err != nil {
		err = errors.New(fmt.Sprintf("%v sql exec fail. Execution process:{sql:%v,err:%v}", event, strSql, err))
		return nil, err
	}
	if f1 == nil {
		return tableComment, nil
	}
	if len(f1.(map[string]any)) == 0 {
		return tableComment, nil
	}
	if v, ok := f1.(map[string]any)["comment"]; ok {
		switch {
		case strings.EqualFold(fmt.Sprintf("%v", v), "<null>"), strings.EqualFold(fmt.Sprintf("%v", v), "<entry>"), len(fmt.Sprintf("%v", v)) == 0:
			tableComment = "<entry>"
		default:
			tableComment = fmt.Sprintf("%v", v)
		}
	}
	return tableComment, nil
}

// IfTableLob 判断表是否存在lob字段
func (ms TableInfoMeta) IfTableLob(s global.TablesMetaInfoInput) (any, error) {
	var (
		event                  = "[sqlserverIfTableLob]"
		f1                     any
		columnName, columnType []string
		err                    error
	)
	strSql := fmt.Sprintf("SELECT COLUMN_NAME as columnName,DATA_TYPE as columnType, CHARACTER_MAXIMUM_LENGTH as dataLength FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE IN ('varbinary') AND (CHARACTER_MAXIMUM_LENGTH > 1000 or CHARACTER_MAXIMUM_LENGTH = -1 ) and TABLE_SCHEMA='%v' and TABLE_NAME='%v'", s.TableInfo.Schema, s.TableInfo.BackendTableName)
	active := ETL.InstanceActiveS{DBType: DBType, DB: s.DB["single"], SqlMode: true}
	if _, f1, err = active.SqlQuery(strSql, ETL.SMap); err != nil {
		err = errors.New(fmt.Sprintf("%v sql exec fail. Execution process:{sql:%v,err:%v}", event, strSql, err))
		return nil, err
	}
	if f1 == nil {
		return nil, err
	}
	for _, v := range f1.([]map[string]any) {
		columnName = append(columnName, fmt.Sprintf("%v", v["columnName"]))
		columnType = append(columnType, fmt.Sprintf("%v(%v)", v["columnType"], v["dataLength"]))
	}
	if len(columnName) > 0 && len(columnType) > 0 {
		return global.TableBigVarcharMeta{
			ColumnName: columnName,
			ColumnType: columnType,
		}, nil
	} else {
		return nil, nil
	}
}

// IfBigVarchar 判断表是否存在varchar>1000
func (ms TableInfoMeta) IfBigVarchar(s global.TablesMetaInfoInput) (any, error) {
	var (
		event                  = "[sqlserverIfBigVarchar]"
		f1                     any
		columnName, columnType []string
		err                    error
	)
	strSql := fmt.Sprintf("SELECT COLUMN_NAME as columnName,DATA_TYPE as dataType, CHARACTER_MAXIMUM_LENGTH as dataLength FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE IN ('varchar', 'nvarchar') AND (CHARACTER_MAXIMUM_LENGTH > 1000 or CHARACTER_MAXIMUM_LENGTH = -1 ) and TABLE_SCHEMA='%v' and table_name='%v'", s.TableInfo.Schema, s.TableInfo.Table)
	active := ETL.InstanceActiveS{DBType: DBType, DB: s.DB["single"], SqlMode: true}
	if _, f1, err = active.SqlQuery(strSql, ETL.SMap); err != nil {
		err = errors.New(fmt.Sprintf("%v sql exec fail. Execution process:{sql:%v,err:%v}", event, strSql, err))
		return nil, err
	}
	if f1 == nil {
		return nil, err
	}
	for _, v := range f1.([]map[string]any) {
		columnName = append(columnName, fmt.Sprintf("%v", v["columnName"]))
		columnType = append(columnType, fmt.Sprintf("%v(%v)", v["dataType"], v["dataLength"]))
	}
	if len(columnName) > 0 && len(columnType) > 0 {
		return global.TableBigVarcharMeta{
			ColumnName: columnName,
			ColumnType: columnType,
		}, nil
	} else {
		return nil, nil
	}
}

func (ms TableInfoMeta) DropPartitionSql(_ global.TablesMetaInfoInput) (any, error) {
	return global.AlterSqlResult{}, nil
}
func (ms TableInfoMeta) AddPartitionSql(_ global.TablesMetaInfoInput) (any, error) {
	return global.AlterSqlResult{}, nil
}
func (ms TableInfoMeta) ModifyPartitionSql(_ global.TablesMetaInfoInput) (any, error) {
	return global.AlterSqlResult{}, nil
}
func (ms TableInfoMeta) Distribution(s global.TablesMetaInfoInput) (any, error) {
	var (
		event       = "[TableDistribution]"
		execTimeout int64
		pp          []global.TableDistribution
	)
	pc, _, _, _ := runtime.Caller(1)
	callingFunc := runtime.FuncForPC(pc)
	callFile, callLine := callingFunc.FileLine(pc)
	callFunc := callingFunc.Name()
	callFuncInfo := fmt.Sprintf("Call functions %v Call File %v:%v", callFunc, callFile, callLine)
	vlog = fmt.Sprintf("(%d) %v %v Start querying the distribution of table %v.%v", s.LogSeq, callFuncInfo, event, s.TableInfo.Schema, s.TableInfo.Table)
	WLog.Debug(vlog)
	//f = append(f, map[string]any{"tableName": fmt.Sprintf("%v.%v", s.TableInfo.Schema, s.TableInfo.Table), "shardMode": "NORMAL", "shardName": "single"})
	pp = append(pp, global.TableDistribution{
		Name:      fmt.Sprintf("%v.%v", s.TableInfo.Schema, s.TableInfo.Table),
		ShardMode: "NORMAL",
		ShardName: "single",
	})
	vlog = fmt.Sprintf("(%d) %v %v A sql statement was executed successfully. sql is {%v}, exec timeout is {%vms}", s.LogSeq, callFuncInfo, event, "no sql", execTimeout)
	WLog.Debug(vlog)
	vlog = fmt.Sprintf("(%d) %v %v The distribution query of table %v.%v is completed. result is {%v}", s.LogSeq, callFuncInfo, event, s.TableInfo.Schema, s.TableInfo.Table, pp)
	WLog.Debug(vlog)
	return pp, nil
}

// indexNameColumnMerge 每个表的索引列数据信息合并及抽取
func (ms TableInfoMeta) indexNameColumnMerge(s global.TablesMetaInfoInput) global.IndexMerge {
	var indexName = make(map[string][]global.IndexColumn)
	var indexType = make(map[string]string)
	var indexColumnSeq = make(map[string][]string)
	var indexNameOrderBy []string
	for _, v := range s.IndexColumn {
		if _, ok := indexType[v.IndexName]; !ok {
			var tmpIndexType string
			switch v.ColumnKey {
			case "P":
				tmpIndexType = "pri"
				v.IndexName = "PRIMARY"
			case "U":
				tmpIndexType = "uni"
			case "M":
				tmpIndexType = "mul"
			}
			indexType[v.IndexName] = tmpIndexType
			indexNameOrderBy = append(indexNameOrderBy, v.IndexName)
		}
		if v1, ok := indexName[v.IndexName]; ok {
			indexName[v.IndexName] = append(v1, v)
		} else {
			indexName[v.IndexName] = []global.IndexColumn{v}
		}
		if v1, ok := indexColumnSeq[v.IndexName]; ok {
			indexColumnSeq[v.IndexName] = append(v1, v.ColumnSeq)
		} else {
			indexColumnSeq[v.IndexName] = []string{v.ColumnSeq}
		}
	}
	return global.IndexMerge{
		Schema:           s.TableInfo.Schema,
		Table:            s.TableInfo.Table,
		IndexColumn:      indexName,
		IndexType:        indexType,
		OrderByIndexName: indexNameOrderBy,
		IndexColumnSeq:   indexColumnSeq,
	}
}
func (ms TableInfoMeta) IndexMerge(s global.TablesMetaInfoInput) global.IndexMerge {
	vlog = fmt.Sprintf("(%d) Start index choice of table %v.%v", s.LogSeq, s.TableInfo.Schema, s.TableInfo.Table)
	if len(s.IndexColumn) == 0 {
		return global.IndexMerge{}
	}
	//索引列处理，联合索引进行列合并
	//去除主键索引列、唯一索引列、普通索引列的所有列明
	p := ms.indexNameColumnMerge(s)
	return p
}

type columnSumGroupS struct {
	singleColumn   []string //索引名
	multipleColumn []string //索引名
}

// 根据索引列数分为单列索引和多列索引
func (ms TableInfoMeta) columnSumGroup(i global.IndexMerge) (q columnSumGroupS) {
	for _, indexName := range i.OrderByIndexName {
		if v, ok := i.IndexColumn[indexName]; ok {
			if len(v) == 1 {
				q.singleColumn = append(q.singleColumn, indexName)
			} else if len(v) > 1 {
				q.multipleColumn = append(q.multipleColumn, indexName)
			}
		}
	}
	return
}

type indexTypeGroupS struct {
	Primary   []string `json:"primary"`   //主键索引名
	UniqueKey []string `json:"uniqueKey"` //唯一索引名
	Key       []string `json:"key"`       //普通索引名
}

// 针对指定的索引名集合，做索引类型做聚合
func (ms TableInfoMeta) indexTypeGroup(indexGarth []string, i global.IndexMerge) (q indexTypeGroupS) {
	for _, v := range indexGarth {
		if key, ok := i.IndexType[v]; ok {
			switch key {
			case "pri":
				q.Primary = append(q.Primary, v)
			case "uni":
				q.UniqueKey = append(q.UniqueKey, v)
			case "mul":
				q.Key = append(q.Key, v)
			}
		}
	}
	return
}

type columnTypeGroupS struct {
	name       string
	class      string //类型
	null       string //null值约束
	dispersion string //离散度
}

// 针对指定的索引名集合，做索引类型做聚合
func (ms TableInfoMeta) columnTypeGroup(indexName string, i global.IndexMerge) (q []columnTypeGroupS) {
	if columnGarth, ok := i.IndexColumn[indexName]; ok {
		for _, v := range columnGarth {
			q = append(q, columnTypeGroupS{
				name:       v.ColumnName,
				class:      v.ColumnType,
				null:       v.Null,
				dispersion: v.Cardinality,
			})
		}
	}
	return
}
func (ms TableInfoMeta) columnTypePriority(q []columnTypeGroupS) (r []int) {
	if len(q) == 0 {
		return
	}
	for _, v := range q {
		switch strings.Split(strings.ToUpper(v.class), "(")[0] {
		case "TINYINT", "SMALLINT", "MEDIUMINT", "INT", "BIGINT": //整数数值类型
			r = append(r, 1)
		case "FLOAT", "DOUBLE": //单精度和双精度的浮点数类型
			r = append(r, 1)
		case "DECIMAL": //定长数值类型
			r = append(r, 1)
		case "DATE", "TIME", "YEAR", "DATETIME", "TIMESTAMP": //时间类型
			r = append(r, 2)
		case "CHAR": //字符串定长
			r = append(r, 3)
		case "VARCHAR": //字符串变长
			r = append(r, 3)
		default:
			r = append(r, 1000)
		}
	}
	return
}
func (ms TableInfoMeta) columnNullGroup(q []columnTypeGroupS) (r []int) {
	for _, v := range q {
		if strings.EqualFold(v.null, "no") {
			r = append(r, 0)
		} else {
			r = append(r, 2)
		}
	}
	return
}
func (ms TableInfoMeta) columnDispersion(q []columnTypeGroupS) (r []int) {
	for _, v := range q {
		if len(v.dispersion) == 0 {
			r = append(r, 0)
			continue
		}
		if t, err := strconv.Atoi(v.dispersion); err != nil {
			r = append(r, 0)
		} else {
			r = append(r, t)
		}
	}
	return
}

func (ms TableInfoMeta) costChoice(i global.IndexMerge) (result global.IndexColumnSumGroup, err error) {
	var (
		indexTypeGarth map[string]any
	)
	type kv struct {
		key   string
		value int
	}

	a := ms.columnSumGroup(i)
	for k1, indexGarth := range [][]string{
		a.singleColumn, a.multipleColumn,
	} {
		q := global.IndexPrioritySort{}
		//比较单、多列索引
		if indexTypeGarth, err = global.StructToMapAny(ms.indexTypeGroup(indexGarth, i)); err != nil {
			return
		}
		var pp = make(map[string]any)
		for k, v := range indexTypeGarth {
			var (
				indexNameGarth   []string
				sortedDispersion []kv
				dispersionPairs  []map[string]any
			)
			if v == nil {
				continue
			}
			switch fmt.Sprintf("%v", reflect.TypeOf(v)) {
			case "[]string":
				indexNameGarth = v.([]string)
			}
			if len(indexNameGarth) == 0 {
				continue
			}
			//按照列的离散度进行排序
			for _, indexName := range indexNameGarth {
				o := ms.columnDispersion(ms.columnTypeGroup(indexName, i))
				total := func() (sum int) {
					for _, dispersion := range o {
						sum += dispersion
					}
					return
				}()
				sortedDispersion = append(sortedDispersion, kv{
					key:   indexName,
					value: total,
				})
			}
			sort.Slice(sortedDispersion, func(m, j int) bool {
				if sortedDispersion[m].value == sortedDispersion[j].value { //离散度相同
					//先比较数据类型优先级
					srcClassPriority := ms.columnTypePriority(ms.columnTypeGroup(sortedDispersion[m].key, i))
					srcClassTotal := func() (sum int) {
						for _, priorityValue := range srcClassPriority {
							sum += priorityValue
						}
						return
					}()

					dstClassPriority := ms.columnTypePriority(ms.columnTypeGroup(sortedDispersion[j].key, i))
					dstClassTotal := func() (sum int) {
						for _, priorityValue := range dstClassPriority {
							sum += priorityValue
						}
						return
					}()
					switch {
					case srcClassTotal > dstClassTotal:
						return sortedDispersion[m].value < sortedDispersion[j].value
					case srcClassTotal < dstClassTotal:
						return sortedDispersion[m].value > sortedDispersion[j].value
					}
					//再比较null值优先级
					srcNullPriority := ms.columnNullGroup(ms.columnTypeGroup(sortedDispersion[m].key, i))
					srcNullTotal := func() (sum int) {
						for _, priorityValue := range srcNullPriority {
							sum += priorityValue
						}
						return
					}()
					dstNullPriority := ms.columnNullGroup(ms.columnTypeGroup(sortedDispersion[j].key, i))
					dstNullTotal := func() (sum int) {
						for _, priorityValue := range dstNullPriority {
							sum += priorityValue
						}
						return
					}()
					switch {
					case srcNullTotal > dstNullTotal:
						return sortedDispersion[m].value < sortedDispersion[j].value
					case srcNullTotal < dstNullTotal:
						return sortedDispersion[m].value > sortedDispersion[j].value
					}
				}
				return sortedDispersion[m].value > sortedDispersion[j].value // 按 value 倒序
			})
			for _, subDispersion := range sortedDispersion {
				dispersionPairs = append(dispersionPairs, map[string]any{"name": subDispersion.key, "column": func() (m []string) {
					for _, col := range ms.columnTypeGroup(subDispersion.key, i) {
						m = append(m, col.name)
					}
					return
				}()})
			}
			pp[k] = dispersionPairs
		}
		var jsonData []byte
		if jsonData, err = json.Marshal(pp); err != nil {
			return
		}
		if err = json.Unmarshal(jsonData, &q); err != nil {
			return
		}
		switch k1 {
		case 0:
			result.Single = q
		case 1:
			result.Multiple = q
		}
	}
	return
}

func (ms TableInfoMeta) IndexOptimizerTrace(s global.TablesMetaInfoInput) (any, error) {
	var (
		result global.IndexColumnSumGroup
		err    error
	)
	vlog = fmt.Sprintf("(%d) Start index choice of table %v.%v", s.LogSeq, s.TableInfo.Schema, s.TableInfo.Table)
	if len(s.IndexColumn) == 0 {
		return nil, nil
	}
	//索引列处理，联合索引进行列合并
	//去除主键索引列、唯一索引列、普通索引列的所有列明
	f1 := ms.IndexMerge(s)
	if global.IsEmptyStruct(f1) {
		//return global.IndexSelection{}
	}
	if result, err = ms.costChoice(f1); err != nil {
		return nil, err
	}
	return result, nil
}

// 一个索引的包含的列类型对比
func (ms TableInfoMeta) typeSelection(tsl []global.IndexColumn) []int {
	var typeDefinition = map[string]int{"int": 1, "time": 2, "string": 3, "text": 4}
	var intType = []string{"TINYINT", "SMALLINT", "INT", "BIGINT", "FLOAT", "REAL", "DECIMAL", "NUMERIC", "MONEY", "SMALLMONEY"}
	var timeType = []string{"DATE", "TIME", "SMALLDATETIME", "DATETIME", "DATETIME2", "DATETIMEOFFSET"}
	var stringType = []string{"CHAR", "NCHAR", "VARCHAR", "NVARCHAR"}
	var typeSeq []int
	for _, v := range tsl {
		var exist = false
		for _, v1 := range intType {
			if strings.HasPrefix(strings.ToUpper(v.ColumnType), v1) {
				typeSeq = append(typeSeq, typeDefinition["int"])
				exist = true
			}
		}
		for _, v1 := range timeType {
			if strings.HasPrefix(strings.ToUpper(v.ColumnType), v1) {
				typeSeq = append(typeSeq, typeDefinition["time"])
				exist = true
			}
		}
		for _, v1 := range stringType {
			if strings.HasPrefix(strings.ToUpper(v.ColumnType), v1) {
				typeSeq = append(typeSeq, typeDefinition["string"])
				exist = true
			}
		}
		if !exist {
			typeSeq = append(typeSeq, 100)
		}
	}
	return typeSeq
}

// 多个索引的数据类型比较，按照规则选择一个或多个相同的
func (ms TableInfoMeta) typeSum(ll map[string][]int) (iName string) {
	iValue := 10000
	var iSlice []int
	for k, v := range ll {
		var iSum int
		for _, v1 := range v {
			iSum += v1
		}
		if iSum < iValue {
			iName = k
			iSlice = v
			iValue = iSum
		} else if iSum == iValue {
			for k2, v2 := range iSlice {
				if v2 < v[k2] {
					iName = k
					iSlice = v
					iValue = iSum
				}
			}
		}
	}
	return
}

// mulCardinality 普通索引通过离散度来进行筛选
func (ms TableInfoMeta) mulCardinality(mulSelection map[string] /*indexType*/ []string /*indexName*/, i global.IndexMerge) any {
	//先检查索引前导列的离散度（第一列离散度值，默认选择一个离散度最高的，如果离散度比较低，建议先analyze table一下）
	var cardinality uint64 = 0
	event := "[mulCardinality]"
	var indexName string
	if len(mulSelection) == 0 {
		return nil
	}
	for _, v := range mulSelection {
		for _, v1 := range v {
			var curryIndexCardinality uint64 = 0
			for _, v2 := range i.IndexColumn[v1] {
				if len(v2.Cardinality) > 0 {
					if parseUint, err2 := strconv.ParseUint(v2.Cardinality, 10, 64); err2 != nil {
						WLog.Error(fmt.Sprintf("(%d) %s string strconv uint64 fail. string is {%v}, error info is %v", 1, event, v2.Cardinality, err2))
					} else {
						if parseUint > curryIndexCardinality {
							curryIndexCardinality = parseUint
						}
					}
				}
			}
			if curryIndexCardinality >= cardinality {
				cardinality = curryIndexCardinality
				indexName = v1
			}
		}
	}
	return map[string]any{"indexName": indexName, "cardinality": cardinality}
}
func (ms TableInfoMeta) keyChoice(i global.IndexMerge) global.IndexSelection {
	//候选索引,先选择主键索引且索引列小于3，然后是唯一索引、普通索引
	var (
		candidateIndex                                             = map[string][]string{"pri": {}, "uni": {}, "mui": {}}
		cardinalityGather                                          []any
		maxCardinalityValue                                        uint64
		cardinalityMaxGather                                       = make(map[string]uint64)
		optimalIndexColumnType                                     []string
		multipleMaxCardinalityGather                               map[string] /*索引名*/ []int //类型定义值
		priSingleSelection, uniSingleSelection, muiSingleSelection = make(map[string][]string), make(map[string][]string), make(map[string][]string)
		priMullSelection, uniMullSelection, muiMullSelection       = make(map[string][]string), make(map[string][]string), make(map[string][]string)
	)
	for k, v := range i.IndexType {
		for _, v1 := range []string{"pri", "uni", "mul"} {
			if strings.EqualFold(v1, v) {
				if len(i.IndexColumn[k]) > 0 {
					candidateIndex[v1] = append(candidateIndex[v1], k)
				}
			}
		}
	}

	//没有索引则退出
	exit := true
	for _, v := range candidateIndex {
		if len(v) > 0 {
			exit = false
			break
		}
	}
	if exit {
		return global.IndexSelection{}
	}
	//筛选主键或者唯一索引为单列的
	for k, v := range candidateIndex {
		var fp, fu, fm []string
		var mp, mu, mm []string
		for _, v1 := range v {
			if v2, ok := i.IndexColumn[v1]; ok {
				if len(v2) == 1 {
					if strings.EqualFold(k, "pri") {
						fp = append(fp, v1)
					} else if strings.EqualFold(k, "uni") {
						fu = append(fu, v1)
					} else if strings.EqualFold(k, "mul") {
						fm = append(fm, v1)
					}
				} else {
					if strings.EqualFold(k, "pri") {
						mp = append(mp, v1)
					} else if strings.EqualFold(k, "uni") {
						mu = append(mu, v1)
					} else if strings.EqualFold(k, "mul") {
						mm = append(mm, v1)
					}
				}
			}
		}
		if len(fp) > 0 {
			priSingleSelection[k] = fp
		}
		if len(fu) > 0 {
			uniSingleSelection[k] = fu
		}
		if len(fm) > 0 {
			muiSingleSelection[k] = fm
		}
		if len(mp) > 0 {
			priMullSelection[k] = mp
		}
		if len(mu) > 0 {
			uniMullSelection[k] = mu
		}
		if len(mm) > 0 {
			muiMullSelection[k] = mm
		}
	}
	cardinalityGather = append(cardinalityGather, ms.mulCardinality(priSingleSelection, i))
	cardinalityGather = append(cardinalityGather, ms.mulCardinality(priMullSelection, i))
	cardinalityGather = append(cardinalityGather, ms.mulCardinality(uniSingleSelection, i))
	cardinalityGather = append(cardinalityGather, ms.mulCardinality(uniMullSelection, i))
	cardinalityGather = append(cardinalityGather, ms.mulCardinality(muiSingleSelection, i))
	cardinalityGather = append(cardinalityGather, ms.mulCardinality(muiMullSelection, i))
	for _, v := range cardinalityGather {
		if v == nil {
			continue
		}
		curryCardinalityValue := v.(map[string]any)["cardinality"].(uint64)
		curryIndexName := fmt.Sprintf("%v", v.(map[string]any)["indexName"])
		if curryCardinalityValue > maxCardinalityValue {
			cardinalityMaxGather = map[string]uint64{
				curryIndexName: curryCardinalityValue,
			}
			maxCardinalityValue = curryCardinalityValue
		} else if curryCardinalityValue == maxCardinalityValue {
			cardinalityMaxGather[curryIndexName] = curryCardinalityValue
		}
	}
	//处理多个离散度相同的索引名
	multipleMaxCardinalityGather = make(map[string][]int)
	for k := range cardinalityMaxGather {
		if v1, ok := i.IndexColumn[k]; ok {
			multipleMaxCardinalityGather[k] = ms.typeSelection(v1)
		}
	}
	//按照数据类型规则找到最优的
	optimalIndexName := ms.typeSum(multipleMaxCardinalityGather)
	for _, v := range i.IndexColumn[optimalIndexName] {
		optimalIndexColumnType = append(optimalIndexColumnType, v.ColumnType)
	}
	return global.IndexSelection{
		IndexType:        i.IndexType[optimalIndexName],
		IndexColumn:      i.IndexColumn[optimalIndexName],
		IndexName:        optimalIndexName,
		IndexCardinality: cardinalityMaxGather[optimalIndexName],
		IndexColumnType:  optimalIndexColumnType,
	}
}
func (ms TableInfoMeta) IndexChoice(s global.TablesMetaInfoInput) (result any, err error) {
	//var (
	//	event = "[IndexChoice]"
	//)
	if len(s.IndexColumn) == 0 {
		return
	}
	//索引列处理，联合索引进行列合并
	//去除主键索引列、唯一索引列、普通索引列的所有列明
	f1 := ms.IndexMerge(s)
	if global.IsEmptyStruct(f1) {
		return
	}
	p := ms.keyChoice(f1)
	return p, nil
}
func (ms TableInfoMeta) getObjectId(s global.TablesMetaInfoInput) (string, error) {
	var (
		f1  any
		err error
	)
	active := ETL.InstanceActiveS{DBType: DBType, DB: s.DB["single"], SqlMode: true}
	strSql := fmt.Sprintf("select object_id('%v.%v') as [objectId]", s.TableInfo.Schema, s.TableInfo.Table)
	if _, f1, err = active.SqlQuery(strSql, ETL.String); err != nil {
		return "", err
	}
	return fmt.Sprintf("%v", f1), err
}
func (ms TableInfoMeta) getIndexName(s global.TablesMetaInfoInput, objectId string) ([]map[string]string, error) {
	var (
		f1         any
		err        error
		indexGarth []map[string]string
	)
	active := ETL.InstanceActiveS{DBType: DBType, DB: s.DB["single"], SqlMode: true}
	strSql := fmt.Sprintf("select "+
		"i.name AS [indexName],\n"+
		"CAST ( i.index_id  AS VARCHAR ( 10 ) ) AS [indexSeq],\n"+
		"CAST ( i.is_unique  AS VARCHAR ( 10 ) ) AS [nonUnique],\n"+
		"CAST (i.is_primary_key AS VARCHAR ( 10 ) ) AS [columnKey]\n"+
		" from \n"+
		"sys.indexes i \n"+
		"where \n"+
		"	object_id = ('%v') \n"+
		"	and is_hypothetical = 0 and is_disabled = 0 AND type > 0 \n"+
		"	order by i.name", objectId)
	if _, f1, err = active.SqlQuery(strSql, ETL.SMap); err != nil {
		return nil, err
	}
	if fmt.Sprintf("%v", reflect.ValueOf(f1)) == "[]" {
		return nil, err
	}
	for _, v := range f1.([]map[string]any) {
		v1 := make(map[string]string)
		for k, vv := range v {
			v1[k] = fmt.Sprintf("%v", vv)
		}
		indexGarth = append(indexGarth, v1)
	}
	return indexGarth, err
}
func (ms TableInfoMeta) getIndexColumnSeq(s global.TablesMetaInfoInput, objectId, indexId string) ([]map[string]string, error) {
	var (
		f1         any
		err        error
		indexGarth []map[string]string
	)
	active := ETL.InstanceActiveS{DBType: DBType, DB: s.DB["single"], SqlMode: true}
	strSql := fmt.Sprintf("select \n"+
		"	index_column_id as [columnSeq],\n"+
		"	column_id as [columnId]\n"+
		"from \n"+
		"	sys.index_columns \n"+
		"where \n"+
		"	object_id = ('%v') and index_id = ('%v') \n"+
		"order by index_column_id", objectId, indexId)
	if _, f1, err = active.SqlQuery(strSql, ETL.SMap); err != nil {
		return nil, err
	}
	if fmt.Sprintf("%v", reflect.ValueOf(f1)) == "[]" {
		return nil, err
	}
	for _, v := range f1.([]map[string]any) {
		v1 := make(map[string]string)
		for k, vv := range v {
			v1[k] = fmt.Sprintf("%v", vv)
		}
		indexGarth = append(indexGarth, v1)
	}
	return indexGarth, err
}
func (ms TableInfoMeta) getIndexColumnHistogram(s global.TablesMetaInfoInput, objectId, indexId string) (string, error) {
	var (
		f1  any
		err error
	)
	active := ETL.InstanceActiveS{DBType: DBType, DB: s.DB["single"], SqlMode: true}
	strSql := fmt.Sprintf("SELECT "+
		"CAST ( "+
		"	CASE \n"+
		"    	WHEN SUM(rows*p.distinct_range_rows) = 0 THEN NULL -- 避免除以零错误\n"+
		"        	ELSE ROUND(CAST(SUM(p.DISTINCT_RANGE_ROWS)* 100 AS FLOAT) / CAST(SUM(rows*p.distinct_range_rows) AS FLOAT), 2) \n"+
		"    	END AS VARCHAR ( 10 ) )"+
		"AS [cardinality]"+
		"FROM \n"+
		"(SELECT \n"+
		"    CASE \n"+
		"        WHEN sps.DISTINCT_RANGE_ROWS = 0 THEN 1 \n"+
		"        ELSE sps.DISTINCT_RANGE_ROWS \n"+
		"    END AS DISTINCT_RANGE_ROWS,\n"+
		"\t\tequal_rows as rows\n"+
		"FROM \n"+
		"    sys.stats s\n"+
		"CROSS APPLY \n"+
		"    sys.dm_db_stats_histogram(s.object_id, s.stats_id) sps\n"+
		"WHERE \n"+
		"    s.object_id = %v AND s.stats_id = %v"+
		"\n) P ", objectId, indexId)
	if _, f1, err = active.SqlQuery(strSql, ETL.String); err != nil {
		return "", err
	}
	switch fmt.Sprintf("%v", f1) {
	case "<nil>":
		return "0", err
	default:
		return fmt.Sprintf("%v", f1), err
	}
}

/*
IndexColumn 查询sql库下指定表的索引统计信息
*/
func (ms TableInfoMeta) IndexColumn(s global.TablesMetaInfoInput) (any, error) {
	var (
		event           = "[sqlServerIndexColumn]"
		result          any
		indexColumnData []global.IndexColumn
		err             error
		columnResult    global.AllReturnResult
		objectId        string
		indexName       []map[string]string
		p               TableColumn
	)

	//var strSql string
	if columnResult, err = p.All(global.GetColumnTypeInput{DB: s.DB, TableInfo: s.TableInfo}); err != nil {
		err = errors.New(fmt.Sprintf("%v get Failed to obtain index information. error is:%v", event, err))
		return nil, err
	}
	if objectId, err = ms.getObjectId(s); err != nil {
		return nil, err
	} else {
		if len(fmt.Sprintf("%v", result)) == 0 {
			return nil, err
		}
	}
	if indexName, err = ms.getIndexName(s, objectId); err != nil {
		return nil, err
	}
	if indexName == nil {
		return nil, err
	}
	for _, v := range indexName {
		var name = v["indexName"] //索引名
		var indexSeq = v["indexSeq"]
		var columnKey = func() string {
			switch v["columnKey"] {
			case "1":
				return "P"
			default:
				switch v["nonUnique"] {
				case "1":
					return "U"
				default:
					return "M"
				}
			}
		}()
		if columnKey == "P" {
			name = "PRIMARY"
		}
		var indexColumnSeq []map[string]string
		var cardinality string
		if indexColumnSeq, err = ms.getIndexColumnSeq(s, objectId, indexSeq); err != nil {
			return nil, err
		}
		var indexNameColumnData []global.TableMeta
		for _, vv := range indexColumnSeq {
			for _, vk := range columnResult.Result {
				if strings.EqualFold(vv["columnId"], fmt.Sprintf("%v", vk.ColumnSeq)) {
					indexNameColumnData = append(indexNameColumnData, vk)
				}
			}
		}
		if cardinality, err = ms.getIndexColumnHistogram(s, objectId, indexSeq); err != nil {
			return nil, err
		}
		for _, key := range indexNameColumnData {
			q := global.IndexColumn{
				Schema:        s.TableInfo.Schema,
				Table:         s.TableInfo.Table,
				IndexName:     name,
				ColumnName:    key.ColumnName,
				ColumnSeq:     fmt.Sprintf("%v", key.ColumnSeq),
				ColumnType:    key.DataType,
				IndexSeq:      indexSeq,
				Null:          key.IsNull,
				ColumnKey:     columnKey,
				IndexCategory: "local",
				Cardinality:   cardinality,
			}
			indexColumnData = append(indexColumnData, q)
		}
	}
	return indexColumnData, err
}

func (ms TableInfoMeta) DropIndexSql(_ global.TablesMetaInfoInput) (any, error) {
	return global.AlterSqlResult{}, nil
}
func (ms TableInfoMeta) AddIndexSql(_ global.TablesMetaInfoInput) (any, error) {
	return global.AlterSqlResult{}, nil
}
func (ms TableInfoMeta) ModifyIndexSql(_ global.TablesMetaInfoInput) (any, error) {
	return global.AlterSqlResult{}, nil
}
func (ms TableInfoMeta) TableName(s global.TablesMetaInfoInput) (result any, err error) {
	var (
		f1           any
		event        = "[mysqlTableName]"
		tableName    string
		returnResult global.ObjectNameReturnResult
	)
	defer func() {
		result = returnResult
	}()
	switch {
	case s.TableInfo.Table == "*":
		tableName = ""
	case strings.Contains(s.TableInfo.Table, "%"):
		tableName = fmt.Sprintf("and table_name like '%v'", s.TableInfo.Table)
	default:
		tableName = ""
	}
	returnResult.BaseResult.Sql = fmt.Sprintf("select table_NAME as `tableName` from information_Schema.TABLES where table_schema ='%v' %v", s.TableInfo.Schema, tableName)
	active := ETL.InstanceActiveS{DBType: DBType, DB: s.DB["single"], StopTime: s.Input.SqlExecStopTime}
	if len(s.DB) == 0 || active.DB == nil {
		err = errors.New(fmt.Sprintf("%v sql exec fail. Execution process:{sql:%v,shard:%v,db:%v,err:%v}", event, returnResult.BaseResult.Sql, "single", s.DB, err))
		return
	}
	if returnResult.BaseResult.TimeOut, f1, err = active.SqlQuery(returnResult.BaseResult.Sql, ETL.StringGarth); err != nil {
		err = errors.New(fmt.Sprintf("%v sql exec fail. Execution process:{sql:%v,err:%v}", event, returnResult.BaseResult.Sql, err))
		return
	}
	if !strings.EqualFold(fmt.Sprintf("%v", reflect.TypeOf(f1)), "[]string") {
		err = errors.New(fmt.Sprintf("%v sql exec fail. result type mismatch! return:%v want:[]string.process:{sql:%v,result:%v}", event, reflect.TypeOf(f1), returnResult.BaseResult.Sql, f1))
		return nil, err
	}
	if f1 == nil {
		return nil, nil
	}
	returnResult.Result = f1.([]string)
	return
}

/*
TableExist sqlserver  获取对应的库表信息
*/
func (ms TableInfoMeta) TableExist(s global.TablesMetaInfoInput) (result global.ObjectExistReturnResult, err error) {
	var (
		strSql string
		event  = "[sqlserverTableExist]"
	)
	strSql = fmt.Sprintf("select count(1) as [sum] from sys.tables where SCHEMA_ID = SCHEMA_ID('%v') and NAME = '%v'", s.TableInfo.Schema, s.TableInfo.Table)
	active := ETL.InstanceActiveS{DBType: DBType, DB: s.DB["single"], SqlMode: true}

	var f1 any
	if _, f1, err = active.SqlQuery(strSql, ETL.Map); err != nil {
		err = errors.New(fmt.Sprintf("%v sql exec fail. Execution process:{sql:%v,err:%v}", event, strSql, err))
		return
	}
	if fmt.Sprintf("%v", f1.(map[string]any)["sum"]) == "1" {
		result.Result = true
	} else {
		result.Result = false
	}
	return
}
func (ms TableInfoMeta) JobExist(s global.TablesMetaInfoInput) (any, error) {
	return nil, nil
}
func (ms TableInfoMeta) DataBaseExist(s global.TablesMetaInfoInput) (bool, error) {
	var (
		event       = "[DataBaseExist]"
		execTimeout int64
		f1          any
		err         error
	)
	pc, _, _, _ := runtime.Caller(1)
	callingFunc := runtime.FuncForPC(pc)
	callFile, callLine := callingFunc.FileLine(pc)
	callFunc := callingFunc.Name()
	callFuncInfo := fmt.Sprintf("Call functions %v Call File %v:%v", callFunc, callFile, callLine)
	vlog = fmt.Sprintf("(%d) %v %v Start checking whether table %v.%v exists.", s.LogSeq, callFuncInfo, event, s.TableInfo.Schema, s.TableInfo.Table)
	WLog.Debug(vlog)
	strSql := fmt.Sprintf("SELECT  count(1) as `sum` FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = '%s' ", s.TableInfo.Schema)
	active := ETL.InstanceActiveS{DBType: DBType, DB: s.DB["single"], SqlMode: true}
	if execTimeout, f1, err = active.SqlQuery(strSql, ETL.Map); err != nil {
		vlog = fmt.Sprintf("(%d) %v %v The execution of a sql statement failed. sql is {%v},error is {%v}", s.LogSeq, callFuncInfo, event, strSql, err)
		WLog.Error(vlog)
		return false, err
	}
	vlog = fmt.Sprintf("(%d) %v %v A sql statement was executed successfully. sql is {%v}, exec timeout is {%vms}", s.LogSeq, callFuncInfo, event, strSql, execTimeout)
	WLog.Debug(vlog)
	if f1 != nil {
		f2 := f1.(map[string]interface{})
		if v, ok := f2["sum"]; ok {
			if d, err1 := strconv.ParseInt(fmt.Sprintf("%s", v), 10, 64); err1 == nil {
				if d > 0 {
					return true, nil
				}
			}
		} else {
			vlog = fmt.Sprintf("(%d) %v %s schema %v row count conversion failed. data is %s, error is %s", s.LogSeq, callFuncInfo, event, s.TableInfo.Schema, v, err)
			WLog.Warn(vlog)
		}
	}
	WLog.Warn(fmt.Sprintf("(%d) %v %s Check whether table a exists or not is completed. result is {%v}", s.LogSeq, callFuncInfo, event, "schema not is Exist!"))
	return false, nil
}
